﻿using System;
using System.Collections.Generic;

using System.Text;
using System.Data.Common;
using System.Data;
using System.Collections;
using System.IO;
using MSL.DataBase.Lib;
using MSL.DataBase.SQL;
using System.Data.OleDb;

namespace MSL.DataBase.Core
{
    /// <summary>
    /// 数据库操作基类
    /// </summary>
    internal abstract class DbBase : IDbHelper, IDisposable
    {
        #region 变量
        private DbProviderFactory _fac = null;         //数据库工厂
        private DbTransaction _tran;
        protected DbConnection _conn = null;
        protected DbCommand _com;

        private DataBaseType _dataBaseType = DataBaseType.Sql;      //数据库类型
        private IsolationLevel _level = IsolationLevel.Unspecified; //数据库隔离级别
        private string _attachInfo = "";              //SQL信息 
        private bool _openTrans = true;               //开启事务
        private bool _writeLog = true;                //是否记录日志 记录日志

        protected int recoedTotal = 0;                //记录总数 一般分页存储过程使用
        protected int batchSize = 2000;               //批量操作每批次记录数
        protected int commandTimeOut = 600;           //超时时间

        #endregion

        #region 属性
        /// <summary>
        /// 开启事务
        /// </summary>
        internal bool OpenTrans
        {
            get { return this._openTrans; }
            set { this._openTrans = value; }
        }
        /// <summary>
        /// 
        /// </summary>
        public DbConnection Con
        {
            get
            {
                return this._conn;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        public DbCommand Com
        {
            get
            {
                return this._com;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        public virtual string Pre
        {
            get
            {
                return "@";
            }
        }
        #endregion

        #region 构造函数
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dataBaseType">数据库类型</param>
        /// <param name="conn">数据库链接</param>
        /// <param name="providerName">数据库程序集 如：System.Data.SqlClient</param>
        public DbBase(DataBaseType dataBaseType, string conn, string providerName)
        {
            if ((conn + "").Length == 0)
            {
                conn = DBConfig.ConnString;
            }
            if (conn.Contains("{0}"))//Access数据库默认存放于App_Data目录下
            {
                conn = String.Format(conn, System.Web.HttpContext.Current.Server.MapPath("~/App_Data/"));
            }
            this._dataBaseType = dataBaseType;
            if (!conn.Contains("path="))//txt path= 文本数据库
            {
                this._fac = DbProviderFactories.GetFactory(providerName);
                this._conn = this._fac.CreateConnection();
                this._conn.ConnectionString = conn;
                this._com = this._conn.CreateCommand();
                this._com.Connection = this._conn;
            }
        }
        #endregion

        #region 执行

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="commandTexts">sql语句</param>
        /// <returns></returns>
        public int ExecSql(string[] commandTexts)
        {
            int affectedRows = 0;
            if (commandTexts == null || commandTexts.Length == 0)
            {
                return 0;
            }
            try
            {
                Open();
                foreach (string sql in commandTexts)
                {
                    SetCommandText(sql, false);
                    affectedRows += this._com.ExecuteNonQuery();
                }
            }
            catch (DbException e)
            {
                WriteLog(e.Message);
            }
            finally
            {
                Close();
            }
            return affectedRows;
        }
        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否存储过程</param>
        /// <returns></returns>
        public int ExecSql(string commandText, bool isProc)
        {
            SetCommandText(commandText, isProc);
            int affectedRows = 1;
            try
            {
                Open();
                this._com.ExecuteNonQuery();
            }
            catch (DbException e)
            {
                affectedRows = 0;
                WriteLog(e.Message);
            }
            finally
            {
                Close();
            }
            return affectedRows;
        }

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否为存储过程</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public int ExecSql(string commandText, bool isProc, DbParameter[] parameters)
        {
            SetCommandText(commandText, isProc);
            int affectedRows = 1;
            try
            {
                Open();
                AddParameters(parameters);
                this._com.ExecuteNonQuery();
            }
            catch (DbException e)
            {
                affectedRows = 0;
                WriteLog(e.Message);
            }
            finally
            {
                Close();
            }
            return affectedRows;
        }
        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否存储过程</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public int ExecSql(string commandText, bool isProc, Hashtable parameters)
        {
            SetCommandText(commandText, isProc);
            int affectedRows = 1;
            try
            {
                Open();
                AddParameters(parameters);
                this._com.ExecuteNonQuery();
            }
            catch (DbException e)
            {
                affectedRows = 0;
                WriteLog(e.Message);
            }
            finally
            {
                Close();
            }
            return affectedRows;
        }

        /// <summary>
        /// 执行多条SQL（Insert/Delete/Update）
        /// </summary>
        /// <param name="listCmdTextAndParams"></param>
        /// <returns></returns>
        public int ExecSql(List<SqlCmdTextAndParams> listCmdTextAndParams)
        {
            int affectedRows = 0;
            try
            {
                Open();
                foreach (SqlCmdTextAndParams cmdTextAndParams in listCmdTextAndParams)
                {
                    this._com.Parameters.Clear();
                    if (cmdTextAndParams.commandText == null || cmdTextAndParams.commandText.Length == 0) continue;

                    if (cmdTextAndParams.parameters != null)
                    {
                        AddParameters(cmdTextAndParams.parameters);
                    }
                    if (cmdTextAndParams.commandParameters != null)
                    {
                        AddParameters(cmdTextAndParams.commandParameters);
                    }
                    this._com.CommandType = cmdTextAndParams.commandType;
                    this._com.CommandText = cmdTextAndParams.commandText;
                    affectedRows += this._com.ExecuteNonQuery();
                }
            }
            catch (DbException e)
            {
                WriteLog(e.Message);
            }
            finally
            {
                Close();
            }
            return affectedRows;
        }
        /// <summary>
        /// 执行Sql事务
        /// </summary>
        /// <param name="sqlList">SQL命令行列表</param>
        public int ExecSql(Hashtable sqlList)
        {
            int succExec = 0;
            foreach (DictionaryEntry myDE in sqlList)
            {
                string cmdText = myDE.Key.ToString();
                DbParameter[] cmdParms = (DbParameter[])myDE.Value;
                this.ExecSql(cmdText, false, cmdParms);
                succExec++;
            }

            return succExec;
        }
        #endregion

        #region 查询
        /// <summary>
        /// 获得DbDataReader
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否存储过程</param>
        /// <returns></returns>
        public DbDataReader ExecReader(string commandText, bool isProc)
        {
            try
            {
                SetCommandText(commandText, isProc);
                Open();
                using (DbDataReader sdr = this._com.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (sdr != null && !sdr.HasRows)
                    {
                        sdr.Dispose();
                    }
                    return sdr;
                }
            }
            catch (DbException e)
            {
                WriteLog(e.Message);
            }
            return null;
        }
        /// <summary>
        /// 获得DbDataReader
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否存储过程</param>
        /// <param name="parameters">参数列表</param>
        /// <returns></returns>
        public DbDataReader ExecReader(string commandText, bool isProc, DbParameter[] parameters)
        {
            try
            {
                SetCommandText(commandText, isProc);
                Open();
                AddParameters(parameters);
                using (DbDataReader sdr = this._com.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    this._com.Parameters.Clear();
                    if (sdr != null && !sdr.HasRows)
                    {
                        sdr.Dispose();
                    }
                    return sdr;
                }
            }
            catch (DbException e)
            {
                WriteLog(e.Message);
            }
            return null;
        }
        /// <summary>
        /// 获得DbDataReader
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否存储过程</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public DbDataReader ExecReader(string commandText, bool isProc, Hashtable parameters)
        {
            try
            {
                SetCommandText(commandText, isProc);
                Open();
                AddParameters(parameters);
                using (DbDataReader sdr = this._com.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    this._com.Parameters.Clear();
                    if (sdr != null && !sdr.HasRows)
                    {
                        sdr.Dispose();
                    }
                }
            }
            catch (DbException e)
            {
                WriteLog(e.Message);
            }
            return null;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否存储过程</param>
        /// <returns></returns>
        public object ExecScalar(string commandText, bool isProc)
        {
            SetCommandText(commandText, isProc);
            object result = null;
            try
            {
                Open();
                result = this._com.ExecuteScalar();
            }
            catch (DbException e)
            {
                WriteLog(e.Message);
            }
            finally
            {
                Close();
            }
            return result;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否存储过程</param>
        /// <returns></returns>
        public T ExecScalar<T>(string commandText, bool isProc)
        {
            object result = ExecScalar(commandText, isProc);
            if (result != null)
            {
                return (T)Convert.ChangeType(result, typeof(T)); ;
            }
            return default(T);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否存储过程</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public object ExecScalar(string commandText, bool isProc, DbParameter[] parameters)
        {
            SetCommandText(commandText, isProc);
            object result = null;
            try
            {
                Open();
                AddParameters(parameters);
                result = this._com.ExecuteScalar();
            }
            catch (DbException e)
            {
                WriteLog(e.Message);
            }
            finally
            {
                Close();
            }
            return result;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否存储过程</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public T ExecScalar<T>(string commandText, bool isProc, DbParameter[] parameters)
        {
            object result = ExecScalar(commandText, isProc, parameters);
            if (result != null)
            {
                return (T)Convert.ChangeType(result, typeof(T)); ;
            }
            return default(T);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否存储过程</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public object ExecScalar(string commandText, bool isProc, Hashtable parameters)
        {
            SetCommandText(commandText, isProc);
            object result = null;
            try
            {
                Open();
                AddParameters(parameters);
                result = this._com.ExecuteScalar();
            }
            catch (DbException e)
            {
                WriteLog(e.Message);
            }
            finally
            {
                Close();
            }
            return result;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否存储过程</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public T ExecScalar<T>(string commandText, bool isProc, Hashtable parameters)
        {
            object result = ExecScalar(commandText, isProc, parameters);
            if (result != null)
            {
                return (T)Convert.ChangeType(result, typeof(T)); ;
            }
            return default(T);
        }
        /// <summary>
        /// 获得DataTable
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否存储过程</param>
        /// <returns></returns>
        public DataTable Query(string commandText, bool isProc)
        {
            SetCommandText(commandText, isProc);
            DataTable dt = null;
            try
            {
                Open();
                using (DbDataAdapter sdr = this._fac.CreateDataAdapter())
                {
                    dt = new DataTable();
                    sdr.SelectCommand = this._com;
                    sdr.Fill(dt);
                }
            }
            catch (DbException e)
            {
                WriteLog(e.Message);
            }
            finally
            {
                Close();
            }
            return dt;
        }
        /// <summary>
        /// 获得DataTable
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否存储过程</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public DataTable Query(string commandText, bool isProc, DbParameter[] parameters)
        {
            SetCommandText(commandText, isProc);
            DataTable dt = null;
            try
            {
                Open();
                AddParameters(parameters);
                using (DbDataAdapter sdr = this._fac.CreateDataAdapter())
                {
                    dt = new DataTable();
                    sdr.SelectCommand = this._com;
                    sdr.Fill(dt);
                }
            }
            catch (DbException e)
            {
                WriteLog(e.Message);
            }
            finally
            {
                Close();
            }
            return dt;
        }
        /// <summary>
        /// 获得DataTable
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="srcDs">填充的数据集</param>
        /// <param name="isProc">是否存储过程</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public DataTable Query(string commandText, bool isProc, Hashtable parameters)
        {
            SetCommandText(commandText, isProc);
            DataTable dt = null;
            try
            {
                Open();
                AddParameters(parameters);
                using (DbDataAdapter sdr = this._fac.CreateDataAdapter())
                {
                    dt = new DataTable();
                    sdr.SelectCommand = this._com;
                    sdr.Fill(dt);
                }
                if (this._com.Parameters.Contains("@TotalCount"))
                {
                    this.recoedTotal = Int32.Parse(this._com.Parameters["@TotalCount"].Value.ToString());
                }
            }
            catch (DbException e)
            {
                WriteLog(e.Message);
            }
            finally
            {
                Close();
            }
            return dt;
        }
        /// <summary>
        /// 获得DataSet
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否存储过程</param>
        /// <param name="srcDs">DataSet集</param>
        /// <returns></returns>
        public DataSet Query(string commandText, bool isProc, string srcDs)
        {
            SetCommandText(commandText, isProc);
            DataSet ds = null;
            try
            {
                Open();
                using (DbDataAdapter sdr = this._fac.CreateDataAdapter())
                {
                    ds = new DataSet();
                    sdr.SelectCommand = this._com;
                    sdr.Fill(ds, srcDs);
                }
            }
            catch (DbException e)
            {
                WriteLog(e.Message);
            }
            finally
            {
                Close();
            }
            return ds;
        }
        /// <summary>
        /// 获得DataTable
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="srcDs">填充的数据集</param>
        /// <param name="isProc">是否存储过程</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public DataSet Query(string commandText, bool isProc, DbParameter[] parameters, string srcDs)
        {
            SetCommandText(commandText, isProc);
            DataSet ds = null;
            try
            {
                Open();
                AddParameters(parameters);

                using (DbDataAdapter sdr = this._fac.CreateDataAdapter())
                {
                    ds = new DataSet();
                    sdr.SelectCommand = this._com;
                    sdr.Fill(ds, srcDs);
                }
            }
            catch (DbException e)
            {
                WriteLog(e.Message);
            }
            finally
            {
                Close();
            }
            return ds;
        }
        /// <summary>
        /// 获得DataTable
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否是存储过程</param>
        /// <param name="srcDs">填充的数据集</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public DataSet Query(string commandText, bool isProc, Hashtable parameters, string srcDs)
        {
            SetCommandText(commandText, isProc);
            DataSet ds = null;
            try
            {
                Open();
                AddParameters(parameters);
                using (DbDataAdapter sdr = this._fac.CreateDataAdapter())
                {
                    ds = new DataSet();
                    sdr.SelectCommand = this._com;
                    sdr.Fill(ds, srcDs);
                }
            }
            catch (DbException e)
            {
                WriteLog(e.Message);
            }
            finally
            {
                Close();
            }
            return ds;
        }
        #endregion

        #region 批量操作 --有待验证

        /// <summary>
        /// 大批量数据插入
        /// </summary>
        /// <param name="table">数据表</param>
        public virtual void BulkInsert(DataTable table)
        {

        }

        /// <summary>
        /// 批量更新数据
        /// </summary>
        /// <param name="table">数据表</param>
        public void BatchUpdate(DataTable table)
        {
            int affectedRows = 1;
            try
            {
                Open();
                DbDataAdapter sdr = this._fac.CreateDataAdapter();
                //设置批量更新的每次处理条数
                sdr.UpdateBatchSize = this.batchSize;
                if (table.ExtendedProperties["SQL"] != null)
                {
                    sdr.SelectCommand.CommandText = table.ExtendedProperties["SQL"].ToString();
                }
                affectedRows = sdr.Update(table);
            }
            catch (DbException ex)
            {
                RollbackTransaction();
                WriteLog(ex.Message);
            }
            finally
            {
                Close();
            }
        }

        /// <summary>
        /// 分批次批量删除数据
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="batchSize">每批次更新记录行数</param>
        /// <param name="interval">批次执行间隔(秒)</param>
        public void BatchDelete(string commandText, int batchSize = 1000, int interval = 1)
        {
            commandText = commandText.ToUpper();

            if (batchSize < 1000) batchSize = 1000;
            if (interval < 1) interval = 1;
            while (ExecReader(commandText.Replace("DELETE", "SELECT TOP 1 1"), false) != null)//是否存在
            {
                commandText = commandText.Replace("DELETE", String.Format("DELETE TOP ({0})", batchSize));
                ExecSql(commandText, false);
                System.Threading.Thread.Sleep(interval * 1000);
            }
        }

        /// <summary>
        /// 分批次批量更新数据
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="batchSize">每批次更新记录行数</param>
        /// <param name="interval">批次执行间隔(秒)</param>
        public void BatchUpdate(string commandText, int batchSize = 1000, int interval = 1)
        {
            if (batchSize < 1000) batchSize = 1000;
            if (interval < 1) interval = 1;

            string existsSql = System.Text.RegularExpressions.Regex.Replace(commandText, @"[\w\s.=,']*from", "select top 1 1 from", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            existsSql = System.Text.RegularExpressions.Regex.Replace(existsSql, @"set[\w\s.=,']* where", "where", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
            existsSql = System.Text.RegularExpressions.Regex.Replace(existsSql, @"update", "select top 1 1 from", System.Text.RegularExpressions.RegexOptions.IgnoreCase);

            while (ExecScalar<Int32>(existsSql, false) != 0)
            {
                ExecSql(System.Text.RegularExpressions.Regex.Replace(commandText, "update", String.Format("UPDATE TOP ({0})", batchSize), System.Text.RegularExpressions.RegexOptions.IgnoreCase), false);
                System.Threading.Thread.Sleep(interval * 1000);
            }
        }

        #endregion

        #region 其它
        /// <summary>
        /// 获取数据库日期函数
        /// </summary>
        /// <returns></returns>
        public string GetDbTime()
        {
            string result = " Getdate() ";
            switch (this._dataBaseType)
            {
                case DataBaseType.Access:
                    result = "'" + DateTime.Now.ToString() + "'";
                    break;
                case DataBaseType.Sql:
                    result = " GetDate() ";
                    break;
                case DataBaseType.Oracle:
                    result = " SYSDATE ";
                    break;
                case DataBaseType.MySql:
                    result = " NOW() ";
                    break;
            }
            return result;
        }
        /// <summary>
        ///  获得Sql字符串相加符号
        /// </summary>
        /// <param name="values">参数值</param>
        /// <returns>字符加</returns>
        public string PlusSign(params string[] values)
        {
            string result = String.Empty;
            switch (this._dataBaseType)
            {
                case DataBaseType.Access:
                case DataBaseType.Sql:
                    if (!String.IsNullOrEmpty(result))
                    {
                        result = result.Substring(0, result.Length - 3);
                    }
                    else
                    {
                        result = " + ";
                    }
                    break;
                case DataBaseType.MySql:
                    result = " CONCAT(";
                    for (int i = 0; i < values.Length; i++)
                    {
                        result += values[i] + " ,";
                    }
                    result = result.Substring(0, result.Length - 2);
                    result += ")";
                    break;
                case DataBaseType.Oracle:
                    for (int i = 0; i < values.Length; i++)
                    {
                        result += values[i] + " || ";
                    }
                    if (!String.IsNullOrEmpty(result))
                    {
                        result = result.Substring(0, result.Length - 4);
                    }
                    else
                    {
                        result = " || ";
                    }
                    break;
            }
            return result;
        }
        /// <summary>
        /// 创建提供程序对数据源类的实现的实例。
        /// </summary>
        /// <returns>数据源类的实现的实例</returns>
        //public abstract DbProviderFactory Instance();
        /// <summary>
        /// 检查参数的安全性
        /// </summary>
        /// <param name="value">参数</param>
        /// <returns>安全的参数</returns>
        public string CheckSqlSafe(string value)
        {
            return value;
        }
        #endregion

        #region 私有方法

        /// <summary>
        /// 设置执行语句
        /// </summary>
        /// <param name="commandText">sql语句或存储过程</param>
        /// <param name="isProc">是否为存储过程</param>
        private void SetCommandText(string commandText, bool isProc)
        {
            this._com.CommandText = commandText;
            this._com.CommandType = isProc ? CommandType.StoredProcedure : CommandType.Text;
            if (isProc && commandText.Contains("sp_Paging") && !this._com.Parameters.Contains("TotalCount"))
            {
                AddReturnPara();
            }
            else
            {
                string checkText = commandText.ToLower();
                //删除表语句 检测
                if (checkText.IndexOf("table") > -1 && (checkText.IndexOf("delete") > -1 || checkText.IndexOf("drop") > -1 || checkText.IndexOf("truncate") > -1))
                {
                    Log.WriteLog(commandText);
                }
            }
            this._attachInfo += "<br><hr>SQL:<br> " + commandText;
            foreach (DbParameter item in this._com.Parameters)
            {
                this._attachInfo += "<br>Para: " + item.ParameterName + "->" + item.Value;
            }
            this._attachInfo += "<hr>";
        }
        /// <summary>
        /// 隔离级别
        /// </summary>
        /// <param name="tranLevel"></param>
        public void SetLevel(IsolationLevel tranLevel)
        {
            this._level = tranLevel;
        }
        /// <summary>
        /// 写日志
        /// </summary>
        /// <param name="e">异常信息</param>
        private void WriteLog(string e)
        {
            if (OnExceptionEvent != null)
            {
                OnExceptionEvent(e);
            }
            if (_tran != null)
            {
                _tran.Rollback();
                _tran = null;
            }
            if (this._writeLog)
            {
                Lib.Log.WriteLog(e + this._attachInfo);
            }
        }
        /// <summary>
        /// 写入sql查询日志
        /// </summary>
        /// <param name="commandText">sql查询</param>
        private void WriteSqlLog(string commandText)
        {
            Lib.Log.WriteLog(DateTime.Now.ToString("yyyyMMdd") + ".txt", this._com.CommandText);
        }

        #endregion

        #region 参数相关
        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="parameters"></param>
        public void AddParameters(DbParameter[] parameters)
        {
            if (parameters != null)
            {
                this._com.Parameters.Clear();
                this._com.Parameters.AddRange(parameters);
            }
        }
        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="parameters">Hashtable</param>
        public void AddParameters(Hashtable parameters)
        {
            DbParameter[] param = new DbParameter[parameters.Count];
            IDictionaryEnumerator ide = parameters.GetEnumerator();
            int i = 0;
            while (ide.MoveNext())
            {
                AddParameters(ide.Key.ToString(), ide.Value);
                i++;
            }
        }
        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="parameterName">参数名</param>
        /// <param name="value">参数值</param>
        public void AddParameters(string parameterName, object value)
        {
            AddParameters(parameterName, value, DbType.String, -1, ParameterDirection.Input);
        }
        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="parameterName">参数名</param>
        /// <param name="value">参数值</param>
        /// <param name="dbType">参数类型</param>
        /// <param name="size">大小</param>
        /// <param name="direction"></param>
        public virtual void AddParameters(string parameterName, object value, DbType dbType, Int32 size, ParameterDirection direction)
        {
            if (this._dataBaseType == DataBaseType.Oracle)
            {
                parameterName = parameterName.Replace(":", "").Replace("@", "");
            }
            else
            {
                parameterName = parameterName.Substring(0, 1) == "@" ? parameterName : "@" + parameterName;
            }
            DbParameter para = GetNewParameter();
            para.ParameterName = parameterName;
            para.Value = value;
            para.DbType = dbType;

            if (size > -1)
            {
                para.Size = size;
            }
            para.Direction = direction;

            Com.Parameters.Add(para);
        }
        /// <summary>
        /// 添加输出参数 主要是分页时输出总记录数
        /// </summary>
        public virtual void AddReturnPara()
        {

        }
        /// <summary>
        /// 获取不同数据库参数实例
        /// </summary>
        /// <returns></returns>
        public virtual DbParameter GetNewParameter()
        {
            return null;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="paraName"></param>
        /// <param name="paraType"></param>
        internal virtual void AddCustomePara(string paraName, ParaType paraType) { }
        #endregion

        #region 分页查询
        /// <summary>
        /// 带分页功能的选择[多条件查询,选择所有时只需把PageIndex/PageSize设置为0]
        /// </summary>
        /// <param name="primaryKey">主键或候选码</param>
        /// <param name="tableName">表名</param>
        /// <param name="showFields">显示字段</param>
        /// <param name="pageIndex">页索引</param>
        /// <param name="pageSize">每页数量[为0时默认选择所有]</param>
        /// <param name="where">查询条件</param>
        /// <param name="sortBy">排序字段</param>
        /// <param name="groupBy">分组字段</param>
        /// <param name="rowCount">返回行数</param>
        /// <returns></returns>
        public virtual DataTable Query(string tableName, string primaryKey, string showFields, int pageIndex, int pageSize, string where, string sortBy, string groupBy, out int rowCount)
        {
            rowCount = 0;
            return null;
        }

        #endregion

        #region 存在性
        /// <summary>
        /// 记录是否存在
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <returns></returns>
        public bool Exists(string commandText)
        {
            int result;
            object obj = ExecScalar(commandText, false);
            if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
            {
                result = 0;
            }
            else
            {
                result = int.Parse(obj.ToString());
            }
            if (result == 0)
            {
                return false;
            }
            return true;
        }
        /// <summary>
        /// 记录是否存在
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否是存储过程</param>
        /// <returns></returns>
        public bool Exists(string commandText, bool isProc)
        {
            int result;
            object obj = ExecScalar(commandText, isProc);
            if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
            {
                result = 0;
            }
            else
            {
                result = int.Parse(obj.ToString());
            }
            if (result == 0)
            {
                return false;
            }
            return true;
        }
        /// <summary>
        /// 记录是否存在
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否是存储过程</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public bool Exists(string commandText, Hashtable parameters, bool isProc)
        {
            int result;
            object obj = ExecScalar(commandText, isProc, parameters);
            if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
            {
                result = 0;
            }
            else
            {
                result = int.Parse(obj.ToString());
            }
            if (result == 0)
            {
                return false;
            }
            return true;
        }
        /// <summary>
        /// 记录是否存在
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public bool Exists(string commandText, Hashtable parameters)
        {
            int result;
            object obj = ExecScalar(commandText, false, parameters);
            if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
            {
                result = 0;
            }
            else
            {
                result = int.Parse(obj.ToString());
            }
            if (result == 0)
            {
                return false;
            }
            return true;
        }
        /// <summary>
        /// 列是否存在
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <returns></returns>
        public bool ColumnExists(string tableName, string columnName)
        {
            return true;
        }
        /// <summary>
        /// 表是否存在  2013-6-15添加，待验证
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public bool TableExists(string tableName)
        {
            int count = 0;
            string commandText;
            if (String.IsNullOrEmpty(tableName))
            {
                throw new ArgumentNullException();
            }

            if ((!tableName.Contains("(") || !tableName.Contains(")")))
            {
                count = 0;
                commandText = String.Empty;
                switch (this._dataBaseType)
                {
                    case DataBaseType.Sql:
                        commandText = String.Format("SELECT count(*) FROM sysobjects WHERE id = OBJECT_ID(N'{0}') AND xtype in (N'U')", tableName);
                        count = Convert.ToInt32(this.ExecScalar(commandText, false));
                        break;
                    case DataBaseType.Access:
                        commandText = String.Format("SELECT {0} FROM MSysObjects WHERE Flags=0 AND Type=1", tableName);
                        count = Convert.ToInt32(this.ExecScalar(commandText, false));
                        break;
                    case DataBaseType.Oracle:
                        commandText = String.Format("SELECT COUNT(*)  From user_objects WHERE object_type='TABLE' and object_name=upper('{0}')", tableName);
                        count = Convert.ToInt32(this.ExecScalar(commandText, false));
                        break;
                    case DataBaseType.MySql:
                    //commandText = String.Format("SELECT count(*)  FROM  `information_schema`.`COLUMNS`  where TABLE_NAME='{0}' and TABLE_SCHEMA='{1}'", tableName, xeeafbbb.DataBase);
                    case DataBaseType.SQLite:
                        commandText = String.Format("SELECT COUNT(*) FROM sqlite_master WHERE type='table' and name='{0}'", tableName);
                        count = Convert.ToInt32(this.ExecScalar(commandText, false));
                        break;
                }
            }
            return count == 0 ? false : true;
        }
        #endregion

        #region 事务

        /// <summary>
        /// 开启事务
        /// </summary>
        public void BeginTransaction()
        {
            try
            {
                if (this._openTrans)
                {
                    _tran = this._conn.BeginTransaction(this._level);
                    this._com.Transaction = _tran;
                }
            }
            catch (Exception e)
            {
                WriteLog(e.Message);
            }
        }
        /// <summary>
        /// 提交事务
        /// </summary>
        public void CommitTransaction()
        {
            this._openTrans = false;
            if (_tran != null)
            {
                try
                {
                    _tran.Commit();
                }
                catch (Exception e)
                {
                    WriteLog(e.Message);
                }
                _tran = null;
            }
        }
        /// <summary>
        /// 事务回滚
        /// </summary>
        public void RollbackTransaction()
        {
            if (_tran != null)
            {
                _tran.Rollback();
            }
        }
        #endregion

        #region 数据库管理

        /// <summary>
        /// 还原数据库
        /// </summary>
        /// <param name="dbName">数据库名</param>
        /// <param name="backfile">备份文件</param>
        /// <returns>还原成功与否提示</returns>
        public virtual bool RestoreDatabase(string dbName, string backfile)
        {
            return false;
        }

        /// <summary>
        /// 备份数据库
        /// </summary>
        /// <param name="dbName">数据库名</param>
        /// <param name="dbFileName">备份文件</param>
        /// <returns>是否备份成功</returns>
        public virtual bool BackDataBase(string dbName, string dbFileName)
        {
            return false;
        }

        /// <summary>
        /// 新建数据库
        /// </summary>
        /// <param name="databaseName">数据库名</param>
        public virtual bool CreateDataBase(string databaseName)
        {
            return false;
        }

        /// <summary>
        /// 删除数据库
        /// </summary>
        public virtual bool DropDataBase(string databaseName)
        {
            return false;
        }
        #endregion

        #region IDisposable 成员

        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            if (this._conn != null)
            {
                Close();
                this._conn = null;
            }
            if (this._com != null)
            {
                this._com = null;
            }
        }
        /// <summary>
        /// 打开数据库链接
        /// </summary>
        public void Open()
        {
            try
            {
                if (this._conn.State == ConnectionState.Closed)
                {
                    this._conn.Open();
                    if (this._openTrans)
                        BeginTransaction();//开启事务
                }
            }
            catch (DbException e)
            {
                WriteLog(e.Message);
            }
        }
        /// <summary>
        /// 关闭数据库链接
        /// </summary>
        public void Close()
        {
            if (this._conn.State == ConnectionState.Open)
            {
                CommitTransaction();//提交事务
                this._conn.Close();
            }
            if (this._com.Parameters != null)
            {
                this._com.Parameters.Clear();
            }
        }
        /// <summary>
        /// 异常委托
        /// </summary>
        /// <param name="msg"></param>
        internal delegate void OnException(string msg);
        /// <summary>
        /// 异常事件
        /// </summary>
        internal event OnException OnExceptionEvent;
        #endregion

    }
    /// <summary>
    /// 特殊参数类型[MProc操作Oracle时用到]
    /// </summary>
    public enum ParaType
    {
        /// <summary>
        /// 游标类型
        /// </summary>
        Cursor,
        /// <summary>
        /// 输出类型
        /// </summary>
        OutPut,
        /// <summary>
        /// 返回值类型
        /// </summary>
        result,
    }
}
